﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
//using System.ComponentModel;


namespace Hanhud.Win
{
    public class ConnectDB: IDisposable
    {     
        public static string ConnectionString = "";
        SqlConnection cnn;       
        
        public ConnectDB()
        {            
            // SqlString dc khoi tao o frmMain_Load
            try
            {
                this.cnn = new SqlConnection(ConnectionString);
                cnn.Open();
                cnn.Close();
            }
            catch
            {
                throw new Exception("Kết nối tới cơ sở dữ liệu thất bại.");
            }
			
		}

        public SqlDataReader CreateDataReader(string sql)
        {
            using (SqlCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = sql;
                return cmd.ExecuteReader(CommandBehavior.SingleResult);
            }
        }

        public void Close()
        {
            if (cnn.State == ConnectionState.Open)
                cnn.Close();
        }

        public void Open()
        {
            if (cnn.State == ConnectionState.Closed)
                cnn.Open();
        }

        public DataTable GetDataTable(string sql, CommandType type, params SqlParameter[] pars)
        {
            DataTable tb = new DataTable();

            using (SqlCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandType = type;
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(pars);

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(tb);
                }
            }
            return tb;        
        }

        public DataSet GetDataSet(string sql, CommandType type)
        {
            DataSet ds = new DataSet();

            using (SqlCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandType = type;
                cmd.CommandText = sql;
                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {
                    da.Fill(ds);
                }
            }
            return ds;        
        }

        public void ExcuteSQL(string sql)
        {            
            Open();
            using (SqlCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandText = sql;
                //cmd.Parameters.AddRange(pars);
                cmd.ExecuteNonQuery();
            }
            Close();                     
            
        }

        public void RunProcedure(string proc, params SqlParameter[] pars)
        {            
            Open();

            using (SqlCommand cmd = cnn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = proc;

                cmd.Parameters.AddRange(pars);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
        
            Close();           
        
        }

        public DataTable ViewProcedure(string proc, params SqlParameter[] pars)
        {
            Open();
            DataTable dt = new DataTable();
            using (SqlCommand cmd = this.cnn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = proc;
                cmd.Parameters.AddRange(pars);

                using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                {

                    da.Fill(dt);
                }
            }
            Close();
            return dt;
        }

        #region Dispose

        private bool disposed = false;

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                // if this is a dispose call dispose on all state you
                // hold, and take yourself off the Finalization queue.
                if (disposing)
                {
                    if (cnn != null)
                    {
                        cnn.Dispose();
                        cnn = null;
                    }
                }

                // free your own state (unmanaged objects)
                //AdditionalCleanup();

                this.disposed = true;
            }
        
        }

        // finalizer simply calls Dispose(false)
        ~ConnectDB()
        {
            Dispose(false);
        }

        #endregion
    }
}
